package com.h3bpm.web.controller;

import OThinker.Common.Data.Database.Parameter;
import OThinker.Common.Data.DbType;
import OThinker.H3.Controller.ControllerBase;
import com.h3bpm.web.utils.DateUtils;
import com.h3bpm.web.utils.StatementUntil;
import data.DataRow;
import data.DataRowCollection;
import data.DataTable;
import net.sf.json.JSONObject;
import org.apache.commons.lang3.time.DateFormatUtils;
import org.apache.poi.hssf.usermodel.HSSFPrintSetup;
import org.apache.poi.hssf.usermodel.HeaderFooter;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.util.*;


/**
 * 报表
 * @author leo
 */

@Component
@RequestMapping("/api/contractStatement")
@RestController
public class ContractStatementController extends ControllerBase {

    private static final Logger logger = LoggerFactory.getLogger(ContractStatementController.class);

    private static final String PROJECT_PATH = System.getProperty("user.dir");

    private static final String RESOURCES_PATH = File.separator + "src" + File.separator + "main" + File.separator + "resources" + File.separator + "excelPath";

    public static final String CONTRACT_STATEMENT_NAME = "test.xlsx";

    public static final String Overdue ="是";

    public static final String notOverdue ="否";

    public final String folderPath = "/Portal/TempImages/storage/";


    @RequestMapping("getContractStatement")
    public Object getContractStatement(String contractNature,String totalMoney,String contractType,String departmentName) {
        JSONObject jsonObject = new JSONObject();
        try {
            XSSFWorkbook sheets = generateExcel(contractNature,totalMoney,contractType,departmentName);
            String realPath = request.getSession().getServletContext().getRealPath("/");
            String realPathName = PROJECT_PATH + RESOURCES_PATH + CONTRACT_STATEMENT_NAME;
            logger.info("....read...write...fileName:" + CONTRACT_STATEMENT_NAME);
            String filename = System.currentTimeMillis() + CONTRACT_STATEMENT_NAME;
            String dirPath = realPath + folderPath;
            File dir = new File(dirPath);
            if(!dir.exists()&&!dir.isDirectory()){
                logger.info("....read...write...create directory:" + dir);
                dir.mkdirs();
            }
            String savePath = dirPath + filename;
            String viewUrl = folderPath +filename;
            File f = new File(savePath);
            FileOutputStream fs = new FileOutputStream(f);
            sheets.write(fs);
            fs.close();
            jsonObject.put("url", viewUrl);
            jsonObject.put("filename", filename);
            jsonObject.put("extension", filename.substring(StatementUntil.CONTRACT_STATEMENT_NAME.lastIndexOf(".") + 1));
            jsonObject.put("success", true);
        } catch (Exception e) {
          e.printStackTrace();
        }
        return jsonObject;
    }


    @RequestMapping("downContractStatement")
    public void downContractStatement(String contractNature, String totalMoney, String contractType, String departmentName, HttpServletRequest request, HttpServletResponse response,String fileName) {
        XSSFWorkbook mWorkbook = null;
        try {
            response.setCharacterEncoding("utf-8");
            String encoderName = URLEncoder.encode(fileName+".xlsx", "UTF-8").replace("+", "%20").replace("%2B", "+");
            response.setHeader("Content-Disposition", "attachment;filename=" + encoderName);
            response.setContentType("application/json");
            response.setHeader("Accept-Ranges", "bytes");
            OutputStream output = null;
            output = response.getOutputStream();
            mWorkbook = generateExcel(contractNature,totalMoney,contractType,departmentName);
            mWorkbook.write(output);
            mWorkbook.close();
            
        }catch (Exception e){
            e.printStackTrace();
        }
    }


    /**
     * 生成Excel
     *
     * @throws IOException
     */
    public XSSFWorkbook generateExcel(String contractNature,String totalMoney,String contractType,String departmentName)throws Exception {
        logger.info("contractNature:  {}",contractNature);
        logger.info("totalMoney:  {}",totalMoney);
        logger.info("contractType:  {}",contractType);
        logger.info("departmentName:  {}",departmentName);
        int jointNum =0;
        if(contractType==null||"".equals(contractType)||"全部".equals(contractType)){
            contractType="全部";
        }else {
           jointNum++;
        }
        if (contractNature==null||"".equals(contractNature)||"全部".equals(contractNature)){
            contractNature="全部";
        }else {
            jointNum++;
        }
        if(departmentName==null||"".equals(departmentName)||"全部".equals(departmentName)){
            departmentName="全部";
        }else {
            jointNum++;
        }
        logger.info("jointNum:  {}",jointNum);
        //创建工作薄
        XSSFWorkbook workbook = new XSSFWorkbook();
        //创建表单
        XSSFSheet sheet = genSheet(workbook, "sheet1");
        /**
         *冻结行列
         *cellNum:表示要冻结的列数；
         *rowNum:表示要冻结的行数；
         *firstCellNum:表示被固定列右边第一列的列号；
         *firstRollNum :表示被固定行下边第一列的行号;
         */
        sheet.createFreezePane(4, 6, 4, 6);
        //创建表单样式
        //创建标题样式
        XSSFCellStyle titleStyle = genTitleStyle(workbook);
        //创建文本样式
        XSSFCellStyle contextStyle = genContextStyle(workbook);
        //创建Excel
        genExcel(sheet, titleStyle, contextStyle,workbook,contractNature,totalMoney,contractType,departmentName,jointNum);

        return workbook;
    }

    /**
     * 合并单元格
     * firstRow 第一行
     * lastRow  最后一行
     * firstCol 第一列
     * lastCol 最后一列
     * lastRow > firstRow || lastCol < firstCol
     * CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
     * 设置生成自定义Excel表头
     * @param sheet        表单
     * @param titleStyle   标题样式
     * @param contextStyle 文本样式
     */
    public void genExcel(XSSFSheet sheet, XSSFCellStyle titleStyle, XSSFCellStyle contextStyle, XSSFWorkbook workbook,String contractNature,String totalMoney,String contractType,String departmentName,int jointNum)throws Exception {
        XSSFCellStyle contextGroundStyle = genGroundContextStyle(workbook);
        setCellWidth(sheet);
        //设置标题位置
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 12));
        //创建第一行，为标题，index从0开始
        XSSFRow row = sheet.createRow(0);
        //设置第一行Height高度
        row.setHeightInPoints(30);
        XSSFCell cell;
        //创建第一列
        cell = row.createCell(0);
        //标题
        cell.setCellValue("公司合同管理汇总报表");
        //设置标题样式，放在赋值之后生效
        cell.setCellStyle(titleStyle);
        //创建第二行
        row = sheet.createRow(1);
        //第二行的合并单元格
        CellRangeAddress regionRow1_1_0 = new CellRangeAddress(1, 1, 0, 1);
        CellRangeAddress regionRow1_1_1 = new CellRangeAddress(1, 1, 2, 4);
        CellRangeAddress regionRow1_1_2 = new CellRangeAddress(1, 1, 5, 7);
        CellRangeAddress regionRow1_1_3 = new CellRangeAddress(1, 1, 8, 12);
        genCellRangerStyle(regionRow1_1_0,sheet);
        genCellRangerStyle(regionRow1_1_1,sheet);
        genCellRangerStyle(regionRow1_1_2,sheet);
        genCellRangerStyle(regionRow1_1_3,sheet);
        cell = row.createCell(0);
        cell.setCellValue("报送单位");
        cell.setCellStyle(contextGroundStyle);
        cell = row.createCell(2);
        cell.setCellValue(departmentName);
        cell.setCellStyle(contextStyle);
        cell = row.createCell(5);
        cell.setCellValue("报送期间");
        cell.setCellStyle(contextGroundStyle);
        cell = row.createCell(8);
        cell.setCellValue("                     年            季");
        cell.setCellStyle(contextStyle);
        // 第三行
        row = sheet.createRow(2);
        CellRangeAddress region2_2_0 = new CellRangeAddress(2, 2, 0, 1);
        CellRangeAddress region2_2_1 = new CellRangeAddress(2, 2, 2, 12);
        genCellRangerStyle(region2_2_0,sheet);
        genCellRangerStyle(region2_2_1,sheet);
        cell = row.createCell(0);
        cell.setCellValue("报送单位类别");
        cell.setCellStyle(contextGroundStyle);
        cell = row.createCell(2);
        cell.setCellValue("合同承办单位（       ）        合同法人授权签订单位（        ）");
        cell.setCellStyle(contextStyle);
        // 第四行
        row = sheet.createRow(3);
        CellRangeAddress region3_3_0 = new CellRangeAddress(3, 3, 8, 11);
        genCellRangerStyle(region3_3_0,sheet);
        cell = row.createCell(0);
        cell.setCellValue("合同签订期间");
        cell.setCellStyle(contextGroundStyle);
        cell = row.createCell(2);
        cell.setCellValue("合同类别（按类别填报）");
        cell.setCellStyle(contextGroundStyle);
        //新加
        cell = row.createCell(3);
        cell.setCellValue("合同性质");
        cell.setCellStyle(contextGroundStyle);

        cell = row.createCell(4);
        cell.setCellValue("新签订");
        cell.setCellStyle(contextGroundStyle);
        cell = row.createCell(6);
        cell.setCellValue("正常履约完成合同（截至目前所有）");
        cell.setCellStyle(contextGroundStyle);
        cell = row.createCell(8);
        cell.setCellValue("超期限合同（截至目前所有）");
        cell.setCellStyle(contextGroundStyle);
        cell = row.createCell(12);
        cell.setCellValue("备注");
        cell.setCellStyle(contextGroundStyle);
        // 第五行
        row = sheet.createRow(4);
        CellRangeAddress region3_4_0 = new CellRangeAddress(3, 4, 4, 5);
        CellRangeAddress region3_4_1 = new CellRangeAddress(3, 4, 6, 7);
        CellRangeAddress region4_4_0 = new CellRangeAddress(4, 4, 8, 9);
        CellRangeAddress region4_4_1 = new CellRangeAddress(4, 4, 10, 11);
        genCellRangerStyle(region3_4_0,sheet);
        genCellRangerStyle(region3_4_1,sheet);
        genCellRangerStyle(region4_4_0,sheet);
        genCellRangerStyle(region4_4_1,sheet);
        cell = row.createCell(8);
        cell.setCellValue("履约完成");
        cell.setCellStyle(contextGroundStyle);
        cell = row.createCell(10);
        cell.setCellValue("仍未履约");
        cell.setCellStyle(contextGroundStyle);
        // 第六行
        row = sheet.createRow(5);
        CellRangeAddress region3_5_0 = new CellRangeAddress(3, 5, 0, 1);
        CellRangeAddress region3_5_1 = new CellRangeAddress(3, 5, 2, 2);
        CellRangeAddress region3_5_2 = new CellRangeAddress(3, 5, 3, 3);
        CellRangeAddress region3_5_3 = new CellRangeAddress(3, 5, 12, 12);
        genCellRangerStyle(region3_5_0,sheet);
        genCellRangerStyle(region3_5_1,sheet);
        genCellRangerStyle(region3_5_2,sheet);
        genCellRangerStyle(region3_5_3,sheet);
        cell = row.createCell(4);
        cell.setCellValue("份数");
        cell.setCellStyle(contextGroundStyle);
        cell = row.createCell(5);
        cell.setCellValue("合同金额");
        cell.setCellStyle(contextGroundStyle);
        cell = row.createCell(6);
        cell.setCellValue("份数");
        cell.setCellStyle(contextGroundStyle);
        cell = row.createCell(7);
        cell.setCellValue("合同金额");
        cell.setCellStyle(contextGroundStyle);
        cell = row.createCell(8);
        cell.setCellValue("份数");
        cell.setCellStyle(contextGroundStyle);
        cell = row.createCell(9);
        cell.setCellValue("合同金额");
        cell.setCellStyle(contextGroundStyle);
        cell = row.createCell(10);
        cell.setCellValue("份数");
        cell.setCellStyle(contextGroundStyle);
        cell = row.createCell(11);
        cell.setCellValue("合同金额");
        cell.setCellStyle(contextGroundStyle);
        genSheetDate(sheet,contextStyle,contractNature,totalMoney,contractType,departmentName,jointNum);
    }

    /**
     * 设置Excel 的列宽
     * @param sheet
     */
    private static void setCellWidth(XSSFSheet sheet){
        //根据Excel列名长度，指定列名宽度  Excel总共12列
        for (int i = 0; i <= 12; i++) {
            if (i == 0) {
                sheet.setColumnWidth(i, 1500);
            } else if (i == 1) {
                sheet.setColumnWidth(i, 8000);
            } else if (i == 2) {
                sheet.setColumnWidth(i, 3500);
            } else if (i == 3) {
                sheet.setColumnWidth(i, 3500);
            } else if (i == 4 || i == 6 || i == 8 || i == 10) {
                sheet.setColumnWidth(i, 2000);
            } else if (i == 5 || i == 7 || i == 9 || i == 11) {
                sheet.setColumnWidth(i, 3000);
            }else if (i == 12 ){
                sheet.setColumnWidth(i, 7000);
            }
        }
    }

    /**
     * 实际填充数据的时候，对可能为空的数据要进行处理，要先进行判断，否则报错
     *   String value = Test.getVal();
     *   if(StringUtils.isNotBlank(value)) {
     *   cell.setCellValue(value);
     *  } else{
     *   cell.setCellValue(" ");
     *  }
     * 从数据库取数据填充到Excel
     * 填充Excel数据
     * @param sheet
     * @param contextStyle
     */
    public void genSheetDate(XSSFSheet sheet, XSSFCellStyle contextStyle, String contractNature, String totalMoney, String contractType,String departmentName,int jointNum) throws Exception {
        int cellNum = 6;
        /**
         * i从6开始计数，因为上面已经创建了 0 1 2 3 4 5行
         */
        Calendar cale = Calendar.getInstance();
        int year = cale.get(Calendar.YEAR);
        String nowQuarter = DateUtils.getNowQuarter();
        HashMap<String, Object> a1 = summationContractQuarterMin(notOverdue, year, contractType, contractNature, "中", totalMoney, nowQuarter,departmentName,jointNum);
        HashMap<String, Object> a2 = summationContractQuarterMin(notOverdue, year, contractType, contractNature, "终结", totalMoney, nowQuarter,departmentName,jointNum);
        HashMap<String, Object> a3 = summationContractQuarterMin(Overdue, year, contractType, contractNature, "终结", totalMoney, nowQuarter,departmentName,jointNum);
        HashMap<String, Object> a4 = summationContractQuarterMin(Overdue, year, contractType, contractNature, "中", totalMoney, nowQuarter,departmentName,jointNum);
        
        HashMap<String, Object> b1 = summationContractQuarterMax(notOverdue, year, contractType, contractNature, "中", totalMoney, nowQuarter,departmentName,jointNum);
        HashMap<String, Object> b2 = summationContractQuarterMax(notOverdue, year, contractType, contractNature, "终结", totalMoney, nowQuarter,departmentName,jointNum);
        HashMap<String, Object> b3 = summationContractQuarterMax(Overdue, year, contractType, contractNature, "终结", totalMoney, nowQuarter,departmentName,jointNum);
        HashMap<String, Object> b4 = summationContractQuarterMax(Overdue, year, contractType, contractNature, "中", totalMoney, nowQuarter,departmentName,jointNum);
        
        HashMap<String, Object> c1 = sumContractMin(notOverdue, year, contractType, contractNature, "中", totalMoney,departmentName,jointNum);
        HashMap<String, Object> c2 = sumContractMin(notOverdue, year, contractType, contractNature, "终结", totalMoney,departmentName,jointNum);
        HashMap<String, Object> c3 = sumContractMin(Overdue, year, contractType, contractNature, "终结", totalMoney,departmentName,jointNum);
        HashMap<String, Object> c4 = sumContractMin(Overdue, year, contractType, contractNature, "中", totalMoney,departmentName,jointNum);
       
        HashMap<String, Object> d1 = sumContractMax(notOverdue, year, contractType, contractNature, "中", totalMoney,departmentName,jointNum);
        HashMap<String, Object> d2 = sumContractMax(notOverdue, year, contractType, contractNature, "终结", totalMoney,departmentName,jointNum);
        HashMap<String, Object> d3 = sumContractMax(Overdue, year, contractType, contractNature, "终结", totalMoney,departmentName,jointNum);
        HashMap<String, Object> d4 = sumContractMax(Overdue, year, contractType, contractNature, "中", totalMoney,departmentName,jointNum);

        HashMap<String, Object> e3 = sumContractMin(Overdue, year-1, contractType, contractNature, "终结", totalMoney,departmentName,jointNum);
        HashMap<String, Object> e4 = sumContractMin(Overdue, year-1, contractType, contractNature, "中", totalMoney,departmentName,jointNum);

        HashMap<String, Object> f3 = sumContractMax(Overdue, year-1, contractType, contractNature, "终结", totalMoney,departmentName,jointNum);
        HashMap<String, Object> f4 = sumContractMax(Overdue, year-1, contractType, contractNature, "中", totalMoney,departmentName,jointNum);

        HashMap<String, Object> g3 = sumContractMin(Overdue, year-2, contractType, contractNature, "终结", totalMoney,departmentName,jointNum);
        HashMap<String, Object> g4 = sumContractMin(Overdue, year-2, contractType, contractNature, "中", totalMoney,departmentName,jointNum);

        HashMap<String, Object> h3 = sumContractMax(Overdue, year-2, contractType, contractNature, "终结", totalMoney,departmentName,jointNum);
        HashMap<String, Object> h4 = sumContractMax(Overdue, year-2, contractType, contractNature, "中", totalMoney,departmentName,jointNum);

        HashMap<String, Object> i3 = sumContractMin(Overdue, year-3, contractType, contractNature, "终结", totalMoney,departmentName,jointNum);
        HashMap<String, Object> i4 = sumContractMin(Overdue, year-3, contractType, contractNature, "中", totalMoney,departmentName,jointNum);

        HashMap<String, Object> j3 = sumContractMax(Overdue, year-3, contractType, contractNature, "终结", totalMoney,departmentName,jointNum);
        HashMap<String, Object> j4 = sumContractMax(Overdue, year-3, contractType, contractNature, "中", totalMoney,departmentName,jointNum);

        HashMap<String, Object> k3 = sumContractYearsMin(Overdue, year-4, contractType, contractNature, "终结", totalMoney,departmentName,jointNum);
        HashMap<String, Object> k4 = sumContractYearsMin(Overdue, year-4, contractType, contractNature, "中", totalMoney,departmentName,jointNum);

        HashMap<String, Object> l3 = sumContractYearsMax(Overdue, year-4, contractType, contractNature, "终结", totalMoney,departmentName,jointNum);
        HashMap<String, Object> l4 = sumContractYearsMax(Overdue, year-4, contractType, contractNature, "中", totalMoney,departmentName,jointNum);
//        sumContractYearsMin(Overdue, year, contractType, contractNature, "", totalMoney,departmentName,jointNum);
        cellNum= titleData(sheet, contextStyle, cellNum, "一", "本季度");
        cellNum = detailData(sheet, contextStyle, cellNum, a1, a2, a3, a4, "1", "   其中：100万元以下合同",contractType,contractNature);
        cellNum = detailData(sheet, contextStyle, cellNum, b1, b2, b3, b4, "2", "        100万元及以上合同",contractType,contractNature);
        cellNum = blankLine(sheet, contextStyle, cellNum);
        cellNum = titleData(sheet, contextStyle, cellNum, "二", "本年度");
        cellNum = detailData(sheet, contextStyle, cellNum, c1, c2, c3, c4, "1", "   其中：100万元以下合同",contractType,contractNature);
        cellNum = detailData(sheet, contextStyle, cellNum, d1, d2, d3, d4, "2", "        100万元及以上合同",contractType,contractNature);
        cellNum = blankLine(sheet, contextStyle, cellNum);
        cellNum = titleData(sheet, contextStyle, cellNum, "三", "本年度");
        cellNum = titleData(sheet, contextStyle, cellNum, "1", "其中：-1年度");
        cellNum = detailData(sheet, contextStyle, cellNum, null, null, e3, e4, "(1)", "   其中：100万元以下合同",contractType,contractNature);
        cellNum = detailData(sheet, contextStyle, cellNum, null, null, f3, f4, "(2)", "        100万元及以上合同",contractType,contractNature);
        cellNum = titleData(sheet, contextStyle, cellNum, "2", "其中：-2年度");
        cellNum = detailData(sheet, contextStyle, cellNum, null, null, g3, g4, "(1)", "   其中：100万元以下合同",contractType,contractNature);
        cellNum = detailData(sheet, contextStyle, cellNum, null, null, h3, h4, "(2)", "        100万元及以上合同",contractType,contractNature);
        cellNum = titleData(sheet, contextStyle, cellNum, "3", "其中：-3年度");
        cellNum = detailData(sheet, contextStyle, cellNum, null, null, i3, i4, "(1)", "   其中：100万元以下合同",contractType,contractNature);
        cellNum = detailData(sheet, contextStyle, cellNum, null, null, j3, j4, "(2)", "        100万元及以上合同",contractType,contractNature);
        cellNum = titleData(sheet, contextStyle, cellNum, "4", "其中：-4及以上年度");
        cellNum = detailData(sheet, contextStyle, cellNum, null, null, k3, k4, "(1)", "   其中：100万元以下合同",contractType,contractNature);
        cellNum = detailData(sheet, contextStyle, cellNum, null, null, l3, l4, "(2)", "        100万元及以上合同",contractType,contractNature);
        genSheetEndnote(sheet, contextStyle, cellNum);
    }
    public static int titleData(XSSFSheet sheet,XSSFCellStyle contextStyle,int i,String firstColumn,String secondColumn){
        XSSFCell cell ;
        XSSFRow row;
        row = sheet.createRow(i++);
        //创建第三行第一列
        cell = row.createCell(0);
        //第三行第一列的值
        cell.setCellValue(firstColumn);
        //设置样式
        cell.setCellStyle(contextStyle);
        //2 row 1 column
        cell = row.createCell(1);
        cell.setCellValue(secondColumn);
        cell.setCellStyle(contextStyle);
        //2 row 2 column
        cell = row.createCell(2);
        cell.setCellStyle(contextStyle);
        //2 row 3 column
        cell = row.createCell(3);
        cell.setCellStyle(contextStyle);
        //2 row 4 column
        cell = row.createCell(4);
        cell.setCellStyle(contextStyle);
        //2 row 5 column
        cell = row.createCell(5);
        cell.setCellStyle(contextStyle);
        //2 row 6 column
        cell = row.createCell(6);
        cell.setCellStyle(contextStyle);
        //2 row 7 column
        cell = row.createCell(7);
        cell.setCellStyle(contextStyle);
        //2 row 8 column
        cell = row.createCell(8);
        cell.setCellStyle(contextStyle);
        //2 row 9 column
        cell = row.createCell(9);
        cell.setCellStyle(contextStyle);
        cell = row.createCell(10);
        cell.setCellStyle(contextStyle);
        cell = row.createCell(11);
        cell.setCellStyle(contextStyle);
        cell = row.createCell(12);
        cell.setCellStyle(contextStyle);
        return i;
    }

    public static int detailData(XSSFSheet sheet, XSSFCellStyle contextStyle, int i, HashMap<String, Object> a, HashMap<String, Object> b, HashMap<String, Object> c, HashMap<String, Object> d, String firstColumn, String secondColumn, String contractType, String contractNature) {
        XSSFCell cell ;
        XSSFRow row;
        row = sheet.createRow(i++);
        //创建第三行第一列
        cell = row.createCell(0);
        //第三行第一列的值
        cell.setCellValue(firstColumn);
        //设置样式
        cell.setCellStyle(contextStyle);
        //2 row 1 column
        cell = row.createCell(1);
        cell.setCellValue(secondColumn);
        cell.setCellStyle(contextStyle);
        //2 row 2 column
        cell = row.createCell(2);
        cell.setCellValue(contractType);
        cell.setCellStyle(contextStyle);
        //2 row 3 column
        cell = row.createCell(3);
        cell.setCellValue(contractNature);
        cell.setCellStyle(contextStyle);
        //2 row 4 column
        cell = row.createCell(4);
        if (a != null) {
            cell.setCellValue(a.get("count").toString());
        }
        cell.setCellStyle(contextStyle);
        //2 row 5 column
        cell = row.createCell(5);
        if (a != null&&a.get("totalMoney")!=null) {
            cell.setCellValue(a.get("totalMoney").toString());
        }
        cell.setCellStyle(contextStyle);
        //2 row 6 column
        cell = row.createCell(6);
        if (b != null) {
            cell.setCellValue(b.get("count").toString());
        }
        cell.setCellStyle(contextStyle);
        //2 row 7 column
        cell = row.createCell(7);
        if (b != null&&b.get("totalMoney")!=null) {
            cell.setCellValue(b.get("totalMoney").toString());
        }
        cell.setCellStyle(contextStyle);
        //2 row 8 column
        cell = row.createCell(8);
        if (c != null) {
            cell.setCellValue(c.get("count").toString());
        }
        cell.setCellStyle(contextStyle);
        //2 row 9 column
        cell = row.createCell(9);
        if (c != null&&c.get("totalMoney")!=null) {
            cell.setCellValue(c.get("totalMoney").toString());
        }
        cell.setCellStyle(contextStyle);
        cell = row.createCell(10);
        if (d != null) {
            cell.setCellValue(d.get("count").toString());
        }
        cell.setCellStyle(contextStyle);
        cell = row.createCell(11);
        if (d != null&&d.get("totalMoney")!=null) {
            cell.setCellValue(d.get("totalMoney").toString());
        }
        cell.setCellStyle(contextStyle);
        cell = row.createCell(12);
        cell.setCellStyle(contextStyle);
        return i;
    }
    public static int blankLine(XSSFSheet sheet,XSSFCellStyle contextStyle,int i){
        XSSFCell cell ;
        XSSFRow row;
        row = sheet.createRow(i++);
        //创建第三行第一列
        cell = row.createCell(0);
        //第三行第一列的值
        //设置样式
        cell.setCellStyle(contextStyle);
        //2 row 1 column
        cell = row.createCell(1);
        cell.setCellStyle(contextStyle);
        //2 row 2 column
        cell = row.createCell(2);
        cell.setCellStyle(contextStyle);
        //2 row 3 column
        cell = row.createCell(3);
        cell.setCellStyle(contextStyle);
        //2 row 4 column
        cell = row.createCell(4);
        cell.setCellStyle(contextStyle);
        //2 row 5 column
        cell = row.createCell(5);
        cell.setCellStyle(contextStyle);
        //2 row 6 column
        cell = row.createCell(6);
        cell.setCellStyle(contextStyle);
        //2 row 7 column
        cell = row.createCell(7);
        cell.setCellStyle(contextStyle);
        //2 row 8 column
        cell = row.createCell(8);
        cell.setCellStyle(contextStyle);
        //2 row 9 column
        cell = row.createCell(9);
        cell.setCellStyle(contextStyle);
        cell = row.createCell(10);
        cell.setCellStyle(contextStyle);
        cell = row.createCell(11);
        cell.setCellStyle(contextStyle);
        cell = row.createCell(12);
        cell.setCellStyle(contextStyle);
        return i;
    }
    /**
     * 设置列表的尾部
     * @param sheet
     * @param contextStyle
     */
    public static void genSheetEndnote(XSSFSheet sheet, XSSFCellStyle contextStyle, int cellNum) {
        XSSFCell cell;
        XSSFRow row;
        Date date = new Date();
        row = sheet.createRow(cellNum);
        CellRangeAddress region26_26_0 = new CellRangeAddress(cellNum, cellNum, 1, 12);
        genCellRangerStyle(region26_26_0, sheet);
        cell = row.createCell(1);
        cell.setCellValue("需要反馈的事项：");
        cell.setCellStyle(contextStyle);
        cell = row.createCell(0);
        cell.setCellValue("四");
        cell.setCellStyle(contextStyle);
        //27
        row = sheet.createRow(cellNum + 1);
        cell = row.createCell(1);
        cell.setCellValue("……（本单位合同管理相关情况：包括但不限于合同签订、合同履行、合同违约、纠纷、诉讼等需要公司层面协调事项等方面。）");
        cell.setCellStyle(contextStyle);
        //28
        row = sheet.createRow(cellNum + 2);
        //29
        row = sheet.createRow(cellNum + 3);
        cell = row.createCell(1);
        cell.setCellValue("备注：(1)上表灰色区域为不使用，不需要填写。(2)本表请于每季度末的下一个月5个工作日内报送。（3）本表一式两份，一份报审计和风险部（法律事务部），一份自存。");
        //30
        row = sheet.createRow(cellNum + 4);
        CellRangeAddress region26_28_0 = new CellRangeAddress(cellNum, cellNum + 2, 0, 0);
        CellRangeAddress region27_28_0 = new CellRangeAddress(cellNum + 1, cellNum + 2, 1, 12);
        CellRangeAddress region29_29_0 = new CellRangeAddress(cellNum + 3, cellNum + 3, 1, 12);

        genCellRangerStyle(region26_28_0, sheet);
        genCellRangerStyle(region27_28_0, sheet);
        sheet.addMergedRegion(region29_29_0);
        cell = row.createCell(1);
        cell.setCellValue("单位主管（签字）：");
        cell = row.createCell(3);
        cell.setCellValue("审核：");
        cell = row.createCell(6);
        cell.setCellValue("填报：");
        cell = row.createCell(11);
        cell.setCellValue("填报日期：");
        cell = row.createCell(12);
        String format = DateFormatUtils.format(date, "yyyy-MM-dd hh:mm:ss");
        logger.info("填报时间： {}",format);
        cell.setCellValue(format);
    }

    private static void genCellRangerStyle(CellRangeAddress cellAddresses,XSSFSheet sheet){
        sheet.addMergedRegion(cellAddresses);
        RegionUtil.setBorderTop(1, cellAddresses, sheet);
        RegionUtil.setBorderBottom(1, cellAddresses, sheet);
        RegionUtil.setBorderLeft(1, cellAddresses, sheet);
        RegionUtil.setBorderRight(1, cellAddresses, sheet);
    }
    /**
     * 设置表单，并生成表单
     *
     * @param workbook  工作簿
     * @param sheetName sheet名字
     * @return
     */
    public static XSSFSheet genSheet(XSSFWorkbook workbook, String sheetName) {
        //生成表单
        XSSFSheet sheet = workbook.createSheet(sheetName);
        //设置表单文本居中
        sheet.setHorizontallyCenter(true);
        sheet.setFitToPage(false);
        //打印时在底部右边显示文本页信息
        Footer footer = sheet.getFooter();
        footer.setRight("Page " + org.apache.poi.hssf.usermodel.HeaderFooter.numPages() + " Of " + org.apache.poi.hssf.usermodel.HeaderFooter.page());
        //打印时在头部右边显示Excel创建日期信息
        Header header = sheet.getHeader();
        header.setRight("Create Date " + org.apache.poi.hssf.usermodel.HeaderFooter.date() + " " + HeaderFooter.time());
        //设置打印方式
        XSSFPrintSetup ps = sheet.getPrintSetup();
        // true：横向打印，false：竖向打印 ，因为列数较多，推荐在打印时横向打印
        ps.setLandscape(true);
        //打印尺寸大小设置为A4纸大小
        ps.setPaperSize(HSSFPrintSetup.A4_PAPERSIZE);
        return sheet;
    }


    /**
     * 创建文本样式
     *
     * @param workbook
     * @return
     */
    public static XSSFCellStyle genContextStyle(XSSFWorkbook workbook) {
        XSSFCellStyle style = workbook.createCellStyle();
        //文本水平居中显示
        style.setAlignment(HorizontalAlignment.CENTER);
        //文本竖直居中显示
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        //文本自动换行
        style.setWrapText(true);

        //生成Excel表单，需要给文本添加边框样式和颜色
        /*
             CellStyle.BORDER_DOUBLE      双边线
             CellStyle.BORDER_THIN        细边线
             CellStyle.BORDER_MEDIUM      中等边线
             CellStyle.BORDER_DASHED      虚线边线
             CellStyle.BORDER_HAIR        小圆点虚线边线
             CellStyle.BORDER_THICK       粗边线
         */
        //设置文本边框
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        //set border color
        //设置文本边框颜色
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        return style;
    }

    public static XSSFCellStyle genGroundContextStyle(XSSFWorkbook workbook) {
        XSSFCellStyle style = workbook.createCellStyle();
        //文本水平居中显示
        style.setAlignment(HorizontalAlignment.CENTER);
        //文本竖直居中显示
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        //文本自动换行
        style.setWrapText(true);

        //生成Excel表单，需要给文本添加边框样式和颜色
        /*
             CellStyle.BORDER_DOUBLE      双边线
             CellStyle.BORDER_THIN        细边线
             CellStyle.BORDER_MEDIUM      中等边线
             CellStyle.BORDER_DASHED      虚线边线
             CellStyle.BORDER_HAIR        小圆点虚线边线
             CellStyle.BORDER_THICK       粗边线
         */
        //设置文本边框
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        //set border color
        //设置文本边框颜色
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        //设置前景填充样式
        style.setFillPattern(FillPatternType.BIG_SPOTS);
        //前景填充色
        style.setFillBackgroundColor(IndexedColors.SEA_GREEN.getIndex());
        style.setFillForegroundColor(IndexedColors.SEA_GREEN.getIndex());
        return style;
    }

    /**
     * 设置生成标题样式
     *
     * @param workbook
     * @return
     */
    public static XSSFCellStyle genTitleStyle(XSSFWorkbook workbook) {
        XSSFCellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setWrapText(true);
        //标题居中，没有边框，所以这里没有设置边框，设置标题文字样式
        XSSFFont titleFont = workbook.createFont();
        //加粗
        titleFont.setBold(true);
        //文字尺寸
        titleFont.setFontHeight((short) 15);
        titleFont.setFontHeightInPoints((short) 15);
        style.setFont(titleFont);
        return style;
    }

    /**
     * 本季度超期/未超期，未履约，已履约，大于一百万
     * @param contractType
     * @param contractNature
     * @param contractStatus
     * @param totalMoney
     * @return
     * @throws Exception
     */
    private HashMap<String, Object> summationContractQuarterMax(String Overdue,int year,String contractType,String contractNature,String contractStatus,String totalMoney,String nowQuarter,String departmentName,int jointNum) throws Exception{
        String updateSql = "select sum(totalMoney) as totalMoneySum ,count(1) as count from i_contractdata WHERE  Overdue = ?  and contractYear = ?  and contractStatus like concat('%',?,'%') and totalMoney >= ? and quarter =?";
        int parameterNum=5;
        Parameter[] params = new Parameter[parameterNum+jointNum];
//        logger.info("应该拼接参数个数 -- parameterNum :  {}",parameterNum+jointNum);
        params[0] = new Parameter("Overdue", DbType.String, Overdue);
        params[1] = new Parameter("contractYear", DbType.String, year);
        params[2] = new Parameter("contractStatus", DbType.String, contractStatus);
        params[3] = new Parameter("totalMoney", DbType.String, totalMoney);
        params[4] = new Parameter("quarter", DbType.String, nowQuarter);
        HashMap<String, Object> paramsHashMap = sqlJoint(contractType, contractNature, departmentName, params, updateSql, parameterNum);
        updateSql = (String) paramsHashMap.get("sql");
        params = (Parameter[]) paramsHashMap.get("parameter");
        DataTable dataTable = getEngine().getPortalQuery().QueryTable(updateSql, params);
        HashMap<String, Object> stringObjectHashMap = new HashMap<>(2);
        DataRowCollection queryRows = dataTable.getRows();
        if (queryRows != null && queryRows.size() > 0) {
            DataRow dataRow = queryRows.get(0);
            String totalMoneySum = dataRow.getString("totalMoneySum");
            String count = dataRow.getString("count");
            stringObjectHashMap.put("count",count);
            stringObjectHashMap.put("totalMoney",totalMoneySum);
            logger.info(year + "年本季度,超期:"+Overdue+"，履约: "+contractStatus+" ,大于一百万的总金额 ：  " + totalMoneySum);
            logger.info(year + "年本季度,超期:"+Overdue+"，履约: "+contractStatus+" ,大于一百万的合同总数  ：  " + count);
            return stringObjectHashMap;
        }
        return null;
    }

    /**
     * 本季度超期/未超期未履约，已履约，小于一百万
     * @param contractType
     * @param contractNature
     * @param contractStatus
     * @param totalMoney
     * @return
     * @throws Exception
     */
    private HashMap<String, Object> summationContractQuarterMin(String Overdue,int year,String contractType,String contractNature,String contractStatus,String totalMoney,String nowQuarter,String departmentName,int jointNum) throws Exception{
        String updateSql = "select sum(totalMoney) as totalMoneySum ,count(1) as count from i_contractdata WHERE  Overdue = ?  and contractYear = ?  and contractStatus like concat('%',?,'%')  and totalMoney < ? and quarter =?";
        int parameterNum=5;
        Parameter[] params = new Parameter[parameterNum+jointNum];
//        logger.info("应该拼接参数个数 -- parameterNum :  {}",parameterNum+jointNum);
        params[0] = new Parameter("Overdue", DbType.String, Overdue);
        params[1] = new Parameter("contractYear", DbType.String, year);
        params[2] = new Parameter("contractStatus", DbType.String, contractStatus);
        params[3] = new Parameter("totalMoney", DbType.String, totalMoney);
        params[4] = new Parameter("quarter", DbType.String, nowQuarter);
        HashMap<String, Object> paramsHashMap = sqlJoint(contractType, contractNature, departmentName, params, updateSql, parameterNum);
        String sql = (String) paramsHashMap.get("sql");
        Parameter[] parameters = (Parameter[]) paramsHashMap.get("parameter");
        DataTable dataTable = getEngine().getPortalQuery().QueryTable(sql, parameters);
        HashMap<String, Object> stringObjectHashMap = new HashMap<>(2);
        DataRowCollection queryRows = dataTable.getRows();
        if (queryRows != null && queryRows.size() > 0) {
            DataRow dataRow = queryRows.get(0);
            String totalMoneySum = dataRow.getString("totalMoneySum");
            String count = dataRow.getString("count");
            stringObjectHashMap.put("count",count);
            stringObjectHashMap.put("totalMoney",totalMoneySum);
            logger.info(year + "年本季度,超期:"+Overdue+"，履约: "+contractStatus+" ,小于一百万的总金额 ：  " + totalMoneySum);
            logger.info(year + "年本季度,超期:"+Overdue+"，履约: "+contractStatus+" ,小于一百万的合同总数  ：  " + count);
            return stringObjectHashMap;
        }
        return null;
    }


    /**
     * 0，-1,-2,-3年超期未履约，已履约大于一百万
     * @param contractType
     * @param contractNature
     * @param contractStatus
     * @param totalMoney
     * @return
     * @throws Exception
     */
    private HashMap<String, Object> sumContractMax(String Overdue,int year,String contractType,String contractNature,String contractStatus,String totalMoney,String departmentName,int jointNum) throws Exception{
        String updateSql = "select sum(totalMoney) as totalMoneySum ,count(1) as count from i_contractdata WHERE  Overdue = ?  and contractYear = ?  and contractStatus like concat('%',?,'%') and totalMoney >= ?";
        int parameterNum=4;
        Parameter[] params = new Parameter[parameterNum+jointNum];
//        logger.info("应该拼接参数个数 -- parameterNum :  {}",parameterNum+jointNum);
        params[0] = new Parameter("Overdue", DbType.String, Overdue);
        params[1] = new Parameter("contractYear", DbType.String, year);
        params[2] = new Parameter("contractStatus", DbType.String, contractStatus);
        params[3] = new Parameter("totalMoney", DbType.String, totalMoney);
        HashMap<String, Object> paramsHashMap = sqlJoint(contractType, contractNature, departmentName, params, updateSql, parameterNum);
        String sql = (String) paramsHashMap.get("sql");
        Parameter[] parameters = (Parameter[]) paramsHashMap.get("parameter");
        DataTable dataTable = getEngine().getPortalQuery().QueryTable(sql, parameters);
        HashMap<String, Object> stringObjectHashMap = new HashMap<>(2);
        DataRowCollection queryRows = dataTable.getRows();
        if (queryRows != null && queryRows.size() > 0) {
            DataRow dataRow = queryRows.get(0);
            String totalMoneySum = dataRow.getString("totalMoneySum");
            String count = dataRow.getString("count");
            stringObjectHashMap.put("count",count);
            stringObjectHashMap.put("totalMoney",totalMoneySum);
            logger.info(year + "年,超期:"+Overdue+"，履约: "+contractStatus+" ,大于一百万的总金额 ：  " + totalMoneySum);
            logger.info(year + "年,超期:"+Overdue+"，履约: "+contractStatus+" ,大于一百万的合同总数  ：  " + count);
            return stringObjectHashMap;
        }
        return null;
    }
    /**
     * 0，-1,-2,-3年超期未履约，已履约小于一百万
     * @param contractType
     * @param contractNature
     * @param contractStatus
     * @param totalMoney
     * @return
     * @throws Exception
     */
    private HashMap<String, Object> sumContractMin(String Overdue,int year,String contractType,String contractNature,String contractStatus,String totalMoney,String departmentName,int jointNum) throws Exception{
        String updateSql = "select sum(totalMoney) as totalMoneySum ,count(1) as count from i_contractdata WHERE  Overdue = ?  and contractYear = ?  and contractStatus like concat('%',?,'%')  and totalMoney < ?";
        int parameterNum=4;
        Parameter[] params = new Parameter[parameterNum+jointNum];
//        logger.info("应该拼接参数个数 -- parameterNum :  {}",parameterNum+jointNum);
        params[0] = new Parameter("Overdue", DbType.String, Overdue);
        params[1] = new Parameter("contractYear", DbType.String, year);
        params[2] = new Parameter("contractStatus", DbType.String, contractStatus);
        params[3] = new Parameter("totalMoney", DbType.String, totalMoney);
        HashMap<String, Object> paramsHashMap = sqlJoint(contractType, contractNature, departmentName, params, updateSql, parameterNum);
        String sql = (String) paramsHashMap.get("sql");
        Parameter[] parameters = (Parameter[]) paramsHashMap.get("parameter");
        DataTable dataTable = getEngine().getPortalQuery().QueryTable(sql, parameters);
        HashMap<String, Object> stringObjectHashMap = new HashMap<>(2);
        DataRowCollection queryRows = dataTable.getRows();
        if (queryRows != null && queryRows.size() > 0) {
            DataRow dataRow = queryRows.get(0);
            String totalMoneySum = dataRow.getString("totalMoneySum");
            String count = dataRow.getString("count");
            stringObjectHashMap.put("count",count);
            stringObjectHashMap.put("totalMoney",totalMoneySum);
            logger.info(year + "年,超期:"+Overdue+"，履约: "+contractStatus+" ,小于一百万的总金额 ：  " + totalMoneySum);
            logger.info(year + "年,超期:"+Overdue+"，履约: "+contractStatus+" ,小于一百万的合同总数  ：  " + count);
            return stringObjectHashMap;
        }
        return null;
    }
    /**
     * -4年以前超期未履约，已履约大于一百万
     * @param contractType
     * @param contractNature
     * @param contractStatus
     * @param totalMoney
     * @return
     * @throws Exception
     */
    private HashMap<String, Object> sumContractYearsMax(String Overdue,int year,String contractType,String contractNature,String contractStatus,String totalMoney,String departmentName,int jointNum) throws Exception{
        String updateSql = "select sum(totalMoney) as totalMoneySum ,count(1) as count from i_contractdata WHERE  Overdue = ?  and contractYear < ?  and contractStatus like concat('%',?,'%')  and totalMoney >= ?";
        int parameterNum=4;
        Parameter[] params = new Parameter[parameterNum+jointNum];
//        logger.info("应该拼接参数个数 -- parameterNum :  {}",parameterNum+jointNum);
        params[0] = new Parameter("Overdue", DbType.String, Overdue);
        params[1] = new Parameter("contractYear", DbType.String, year);
        params[2] = new Parameter("contractStatus", DbType.String, contractStatus);
        params[3] = new Parameter("totalMoney", DbType.String, totalMoney);
        HashMap<String, Object> paramsHashMap = sqlJoint(contractType, contractNature, departmentName, params, updateSql, parameterNum);
        String sql = (String) paramsHashMap.get("sql");
        Parameter[] parameters = (Parameter[]) paramsHashMap.get("parameter");
        DataTable dataTable = getEngine().getPortalQuery().QueryTable(sql, parameters);
        HashMap<String, Object> stringObjectHashMap = new HashMap<>(2);
        DataRowCollection queryRows = dataTable.getRows();
        if (queryRows != null && queryRows.size() > 0) {
            DataRow dataRow = queryRows.get(0);
            String totalMoneySum = dataRow.getString("totalMoneySum");
            String count = dataRow.getString("count");
            stringObjectHashMap.put("count",count);
            stringObjectHashMap.put("totalMoney",totalMoneySum);
            logger.info(year + "年以前,超期:"+Overdue+"，履约: "+contractStatus+" ,大于一百万的总金额 ：  " + totalMoneySum);
            logger.info(year + "年以前,超期:"+Overdue+"，履约: "+contractStatus+" ,大于一百万的合同总数  ：  " + count);
            return stringObjectHashMap;
        }
        return null;
    }

    /***
     * -4年以前超期未履约，已履约小于一百万
     * @param contractType
     * @param contractNature
     * @param contractStatus
     * @param totalMoney
     * @return
     * @throws Exception
     */
    private HashMap<String, Object> sumContractYearsMin(String Overdue,int year,String contractType,String contractNature,String contractStatus,String totalMoney,String departmentName,int jointNum) throws Exception{
        String updateSql = "select sum(totalMoney) as totalMoneySum ,count(1) as count from i_contractdata WHERE  Overdue = ?  and contractYear < ?  and contractStatus like  concat('%',?,'%')  and totalMoney < ?";
        int parameterNum=4;
        Parameter[] params = new Parameter[parameterNum+jointNum];
//        logger.info("应该拼接参数个数 -- parameterNum :  {}",parameterNum+jointNum);
        params[0] = new Parameter("Overdue", DbType.String, Overdue);
        params[1] = new Parameter("contractYear", DbType.String, year);
        params[2] = new Parameter("contractStatus", DbType.String, contractStatus);
        params[3] = new Parameter("totalMoney", DbType.String, totalMoney);
        HashMap<String, Object> paramsHashMap = sqlJoint(contractType, contractNature, departmentName, params, updateSql, parameterNum);
        String sql = (String) paramsHashMap.get("sql");
        Parameter[] parameters = (Parameter[]) paramsHashMap.get("parameter");
        DataTable dataTable = getEngine().getPortalQuery().QueryTable(sql, parameters);
        HashMap<String, Object> stringObjectHashMap = new HashMap<>(2);
        DataRowCollection queryRows = dataTable.getRows();
        if (queryRows != null && queryRows.size() > 0) {
            DataRow dataRow = queryRows.get(0);
            String totalMoneySum = dataRow.getString("totalMoneySum");
            String count = dataRow.getString("count");
            stringObjectHashMap.put("count",count);
            stringObjectHashMap.put("totalMoney",totalMoneySum);
            logger.info(year + "年以前,超期:"+Overdue+"，履约: "+contractStatus+" ,小于一百万的总金额 ：  " + totalMoneySum);
            logger.info(year + "年以前,超期:"+Overdue+"，履约: "+contractStatus+" ,小于一百万的合同总数  ：  " + count);
            return stringObjectHashMap;
        }
        return null;
    }

    private HashMap<String,Object> sqlJoint(String contractType, String contractNature, String departmentName, Parameter[] params, String JointSql, int parameterNum) throws Exception{
        HashMap<String, Object> objectObjectHashMap = new HashMap<>();
//        logger.info("参数个数-- parameterNum :  {}",parameterNum);
        parameterNum--;
        if("全部".equals(contractType)){
        }else {
            parameterNum++;
            JointSql = JointSql.concat(" and contractType = ?");
            params[parameterNum] = new Parameter("contractType", DbType.String, contractType);
//            logger.info("拼接参数个数-0 -- parameterNum :  {}",parameterNum);
//            logger.info("拼接sql-0 -- JointSql :  {}",JointSql);
        }
        if ("全部".equals(contractNature)){
        }else {
            parameterNum++;
            JointSql = JointSql.concat(" and expensesOrReceipts = ?");
            params[parameterNum] = new Parameter("expensesOrReceipts", DbType.String, contractNature);
//            logger.info("拼接参数个数-1 -- parameterNum :  {}",parameterNum);
//            logger.info("拼接sql-1 -- JointSql :  {}",JointSql);
        }
        if("全部".equals(departmentName)){
        }else {
            parameterNum++;
            JointSql = JointSql.concat(" and createdByDepartment = ?");
            params[parameterNum] = new Parameter("createdByDepartment", DbType.String, departmentName);
//            logger.info("拼接参数个数-2 -- parameterNum :  {}",parameterNum);
//            logger.info("拼接sql-2 -- JointSql :  {}",JointSql);
        }
        logger.info("最终拼接sql -- JointSql:  {}",JointSql);
        logger.info("最终拼接参数个数 -- parameterNum :  {}",parameterNum);
        objectObjectHashMap.put("sql",JointSql);
        objectObjectHashMap.put("parameter",params);
        return objectObjectHashMap;
    }
    @Override
    public String getFunctionCode() {
        return null;
    }
}
